package com.guashu.jaywayjsonpath.renhang;

import com.alibaba.fastjson.JSON;
import com.guashu.jaywayjsonpath.util.DealMessageUtil;
import com.jayway.jsonpath.JsonPath;
import com.jayway.jsonpath.PathNotFoundException;
import org.springframework.util.CollectionUtils;

import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DynamicSQLExecutor {
    public static void main(String[] args) {
        String user = "root";
        String password = "root";
        String url = "jdbc:mysql://localhost:3306/renhang?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC";
        String jsonFilePath = "E:\\国银租赁\\需求\\20240201-人行指标\\人行报文.json"; // JSON文件路径
        Connection conn = null;
        try {
            System.out.println("开始解析人行报文...");
            String json = new String(Files.readAllBytes(Paths.get(jsonFilePath)), StandardCharsets.UTF_8);
            //被查者身份证号
            String pa01bi01 = safelyReadJsonPath(json, "$.PRH.PA01.PA01B.PA01BI01");
            conn = DriverManager.getConnection(url, user, password);
            conn.setAutoCommit(false);

            DatabaseUtil dbUtil = new DatabaseUtil();
            Map<String, List<FieldInfo>> tableMap = dbUtil.queryTableStructure("business_parse_rule_renhang_v4");

            for (Map.Entry<String, List<FieldInfo>> entry : tableMap.entrySet()) {
                String tableName = entry.getKey(); // 获取表名
                List<FieldInfo> fieldsTmp = entry.getValue(); // 获取该表的字段信息列表
                // 生成不同的插入语句
                List<FieldInfo> fields = sortByStep(fieldsTmp);
                String sql = generateInsertSQL(tableName, fields);
                PreparedStatement pstmt = conn.prepareStatement(sql);
                // 根据fieldJsonPaths解析json并准备插入数据库
                //executeBatchInsert(pstmt, json, fields, pa01bi01);
                batchInsert(pstmt, json, getRuleMap(fields), pa01bi01);
                pstmt.executeBatch();
                conn.commit();
                pstmt.close();
            }

            System.out.println("解析人行报文入库完成!");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    private static void batchInsert(PreparedStatement pstmt, String json, Map<String,String> ruleMap, String pa01bi01) throws SQLException {

        for (Map.Entry<String, String> entry : ruleMap.entrySet()) {
            String field = entry.getKey();
            String value = entry.getValue();
            String step = ruleMap.get(field);
        }


        pstmt.addBatch();
    }
    private static Map<String,String>  getRuleMap(List<FieldInfo> fields){
        Map<String,String> map=new HashMap<>();
        for(FieldInfo fieldInfo:fields){
            String fieldKey=fieldInfo.getRuleKey();
            String step=fieldInfo.getStep();
            map.put(fieldKey,step);
        }
        return map;
    }
    private static void executeBatchInsert(PreparedStatement pstmt, String json, List<FieldInfo> fields, String pa01bi01) throws SQLException {
        // 对于JSON中的每个字段，使用JsonPath读取值并设置PreparedStatement参数
        List<FieldInfo> newFieldsNoArray = getNewFieldsNoArray(fields);
        List<FieldInfo> newFieldsOneDimensionalArray = getNewFieldsOneDimensionalArray(fields);
        List<FieldInfo> newFieldsTwoDimensionalArray = getNewFieldsTwoDimensionalArray(fields);
        // 初始化参数计数器
        int count = 0;


        //类似于排列组合。把pa01bi01 可能出现的组合全部取完。
        if (!CollectionUtils.isEmpty(newFieldsNoArray)) {
            for (FieldInfo field : newFieldsNoArray) {
                String fieldKey=field.getRuleKey();
                String value = safelyReadJsonPath(json, field.getStep());
                pstmt.setString(++count, value);
            }
        }
        if (!CollectionUtils.isEmpty(newFieldsOneDimensionalArray)) {
            for (FieldInfo field : newFieldsOneDimensionalArray) {
                String step = field.getStep();
                //eg：根据  jason path表达式,"$.PDA.PD01[*].PD01A.PD01AD01"返回 $.PDA.PD01[*]
                String stepArray = DealMessageUtil.getSubstringLeft(step);
                List<Object> records = JsonPath.read(json, stepArray);
                for (Object record : records) {
                    String recordJson = JSON.toJSONString(record);
                    //eg：根据  jason path表达式,"$.PDA.PD01[*]" 返回 $.PD01A.PD01AD01
                    String value = safelyReadJsonPath(recordJson, DealMessageUtil.getSubstringRight(step));
                    pstmt.setString(++count, value); // PreparedStatement的参数索引从1开始
                }

            }
        }

        if (!CollectionUtils.isEmpty(newFieldsTwoDimensionalArray)) {
            for (FieldInfo field : newFieldsOneDimensionalArray) {
                String step = field.getStep();
                //eg：根据 jason path表达式,"$.PDA.PD01[*].PD01E.PD01EH[*].PD01ER03" 返回 $.PDA.PD01[*]
                List<Object> records = JsonPath.read(json, DealMessageUtil.getFirstArray(step));
                for (Object record : records) {
                    String recordJson = JSON.toJSONString(record);
                    //eg：根据 jason path表达式,"$.PDA.PD01[*].PD01E.PD01EH[*].PD01ER03" 返回 $.PD01E.PD01EH[*]
                    List<Object> recordsTwoDimensional = JsonPath.read(recordJson, DealMessageUtil.getMidderArray(step));
                    for (Object recordTwo : recordsTwoDimensional) {
                        String recordTwoJson = JSON.toJSONString(recordTwo);
                        //eg：根据  jason path表达式,"$.PDA.PD01[*].PD01E.PD01EH[*].PD01ER03" 返回 $.PD01ER03
                        String value = safelyReadJsonPath(recordTwoJson, DealMessageUtil.getLast(step));
                        pstmt.setString(++count, value);
                    }
                }

            }
        }
        // 获取参数个数
        int parameterCount = pstmt.getParameterMetaData().getParameterCount();
        // 设置后续参数为null
        for (int i = ++count; i < parameterCount; i++) {
            pstmt.setNull(i, java.sql.Types.NULL);
        }
        pstmt.setString(parameterCount, pa01bi01);
        pstmt.addBatch();
    }

    private static String generateInsertSQL(String tableName, List<FieldInfo> fields) {
        StringBuilder columns = new StringBuilder();
        StringBuilder placeholders = new StringBuilder();
        for (FieldInfo field : fields) {
            if (columns.length() > 0) {
                columns.append(", ");
                placeholders.append(", ");
            }
            columns.append(field.getRuleKey());
            placeholders.append("?");
        }
        return String.format("INSERT INTO %s (%s, pa01bi01, create_time, update_time) VALUES (%s, ?, now(), now())", tableName, columns, placeholders);
    }

    private static String safelyReadJsonPath(String json, String path) {
        try {
            return JsonPath.read(json, path);
        } catch (PathNotFoundException e) {
            return null;
        }
    }


    private static List<FieldInfo> sortByStep(List<FieldInfo> fields) {
        List<FieldInfo> newFieldsNoArray = getNewFieldsNoArray(fields);
        List<FieldInfo> newFieldsOneDimensionalArray = getNewFieldsOneDimensionalArray(fields);
        List<FieldInfo> newFieldsTwoDimensionalArray = getNewFieldsTwoDimensionalArray(fields);
        List<FieldInfo> sortNewFiedList = new ArrayList<>();
        sortNewFiedList.addAll(newFieldsNoArray);
        sortNewFiedList.addAll(newFieldsOneDimensionalArray);
        sortNewFiedList.addAll(newFieldsTwoDimensionalArray);
        return sortNewFiedList;
    }

    private static List<FieldInfo> getNewFieldsNoArray(List<FieldInfo> fields) {
        List<FieldInfo> newFields = new ArrayList<>();
        for (FieldInfo field : fields) {
            if (DealMessageUtil.notContainsAsterisk(field.getStep())) {
                newFields.add(field);
            }
        }
        return newFields;
    }

    private static List<FieldInfo> getNewFieldsOneDimensionalArray(List<FieldInfo> fields) {
        List<FieldInfo> newFields = new ArrayList<>();
        for (FieldInfo field : fields) {
            if (DealMessageUtil.containsOneAsterisk(field.getStep())) {
                newFields.add(field);
            }
        }
        return newFields;
    }

    private static List<FieldInfo> getNewFieldsTwoDimensionalArray(List<FieldInfo> fields) {
        List<FieldInfo> newFields = new ArrayList<>();
        for (FieldInfo field : fields) {
            if (DealMessageUtil.containsTwoAsterisks(field.getStep())) {
                newFields.add(field);
            }
        }
        return newFields;
    }


}
